[Fivetran] S3に置いてる色々な形式のファイルをDWHに同期してみた

Avro基地降下作戦

モダンデータスタック(MDS)

#Fivetran

#データ分析

#Amazon S3

#CSV

#JSON

たまちゃん

2021.06.09

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

大阪オフィスの玉井です。

Fivetranは、対応データソースとして、Amazon S3やGoogle Cloud Storageなどのクラウドストレージに対応しています。もう少し厳密にいうと、S3等に入っている各種ファイルを、DWHにロードすることができます。

今回は、ファイル形式や中身によって、DWHにどういう形でロードされるのかを、いくつか試してみました。

対応ファイルなど

どういう形式のファイルだったらいけるのか？等の公式仕様については、下記のドキュメントにまとまっています。

ファイル形式については、csvがもちろん対応のこと、JsonやParquetといった、半構造化データにも対応しています。こういったデータが、DWHのテーブルにどのような形でロードされるのか、とても気になりますね。

また、（対応している形式であれば）圧縮されていても問題ありません。zipとかtarとかになっていても、その中に入ってるファイルがcsvとかであれば、問題なく取り込むことができます。

注意点は文字コードでしょうか。海外のサービスなので、当然のことながらShift-JISなんかは対象外なので、UTF-8あたりに変換かましておくのが無難そうです。

ちなみに、（私もドキュメントを読んで知ったのですが）、クラウドストレージ以外にも、FTPやSFTPに対応しているため、例えば、オンプレのファイルサーバーからDWHへ同期させることもできます。

やってみた

環境

Fivetran
S3
Google BigQuery

1. Avroファイル

いきなりマニアックな形式のファイルがきましたね。ぶっちゃけますが、私にAvroファイルがどういうものなのかを詳しく語れるだけの知識はありません。

ざっくり調べたところ、「スキーマ情報が内包されている」、「データ部分はバイナリエンコードされている」、といったあたりが特徴的なファイル形式のようです。主にHadoopあたりで使われている形式っぽいですが、BigQueryもavroと相性が良いみたいです。

詳細については、各自ググってください…。

使用するデータ

そんなマニアックなavro形式のデータをどこから持ってくるのか？ですが、s3://athena-examples-リージョン名/flightというパブリックバケットに、avro形式のサンプルデータがあるので、これを使います（本来はAmazon Athenaを触る人向けのサンプルデータ）。

Fivetranの設定

それでは、早速、このS3バケットをConnectorとして登録します。

Connectorの設定は下記のようにしました。

パブリックバケットなので、Public?というトグルをON
同期したいavro形式のファイルが置かれているパスをFolder Pathに指定
File typeにavroを指定
- ちなみにinferという値を指定すると、Fivetran側で取り込む対象のファイルを勝手に判断してくれます。
- 色々な形式のファイルが混在している場合は素直に指定した方が無難でしょう。

flight/avro/下には、さらにyear=1998、year=2002といった形で、年別にフォルダが分かれており、それぞれにavroファイルが配置されています。この検証では、全フォルダ（の中のavro）を同期対象としたいため、パス指定はflight/avro/としました。

設定が問題ない場合、チェックが通ります。

SaaSやDBでは、同期したいテーブルやカラムを選びますが、今回のようなストレージ（の中のファイル）の場合は、同期対象を選ぶ概念はありません（どのファイルを取り込むかは、先程の設定で決まるため）。

あとは、同期を開始するだけです。

DWH側を確認

データ部分がバイナリ化されているavroファイルですが、ちゃんとテーブルとしてロードされていました。

一応、Amazon Athenaのドキュメントを確認しましたが、おそらくカラムも全部揃っていると思います（ドキュメントの中身が、ファイルの仕様ではなく、ただのチュートリアルなので、参考程度）。

ちなみに、26.79GBのデータの同期に、5時間29分かかりました。そのうちBigQueryへのロード時間は約4分でした。バケットからデータを持ってくるほうが、圧倒的に時間がかかるみたいです（増分更新なので、2回目以降はこんなにかからないと思います）。

2. bz2形式に圧縮されたCSVファイル

次は、圧縮済のcsvファイルを試します。

使用するデータ

先程と同じくs3://athena-examples-リージョン名/flightを使います（CSVファイルもある）。

Fivetranの設定

設定は下記の通り。

パブリックバケットなので、Public?というトグルをON
同期したいファイルが置かれているパスをFolder Pathに指定
File typeにcsvを指定
compressionにbz2を指定
- compressionにもinferが指定できます
File Patternは後述

今回はFile Patternに正規表現を入れています。ここの正規表現で、同期対象のファイルやフォルダをコントロールすることができます。今回は2000年以降のフォルダだけを対象にしてみたかったので、このような正規表現を入れました。

ちなみに、Advancedオプションという細かい設定もあり、ここでcsvファイルの詳細を設定することができます。今回はエスケープ文字と区切り文字を明示的に指定しました。基本的には、ここまで明示的に指定しなくても、大体はFivetranが自動で判断してよしなに取り込んでくれます。

正規表現とかが間違ってなければ（記述が問題なければ）チェックが通ります。

DWH側を確認

圧縮されてようが、設定をちゃんとすれば、問題なく取り込むことができます。しかし…。

ヘッダーがアレなことに…。というのも、今回のcsvファイル、ヘッダー（列名）が無いため、1行目がカラム名になってしまいました。残念ながら、ヘッダーをFivetranで付与することはできません（元々あるヘッダーを飛ばすことはできる）。こういう場合は、事前に付与しておく必要がありますね。

ちなみに、84.78GBのデータを同期するのに、20時間51分かかりました。そのうち、BigQueryへのロード時間は約14分でした。

3. JSONファイル

「最近取り込みたいデータ形式ランキング」があるとしたら、絶賛急浮上中なのがjsonファイルではないでしょうか。最近のアプリが吐くログ形式、大体jsonですよね（偏見）。

使用するデータ

下記を使って生成したダミーデータを使いました。

検証するjsonのパターン

jsonはファイルによって中身の構造が結構異なるため、いくつかのパターンを用意しました。

1 構造が全て同じパターン

[
    {
      "id": 0,
      "name": "Lorelei Verde",
      "work": "WestGate",
      "email": "[email protected]",
      "dob": "1951",
      "address": "21 Crockett Street",
      "city": "Melbourne",
      "optedin": true
    },
    {
      "id": 1,
      "name": "Cyrus Friedrich",
      "work": "Terralabs",
      "email": "[email protected]",
      "dob": "1943",
      "address": "86 Templeton Parkway",
      "city": "Coventry",
      "optedin": true
    },
    {
      "id": 2,
      "name": "Travis Lenihan",
      "work": "VenStrategy",
      "email": "[email protected]",
      "dob": "1956",
      "address": "52 Hinton Road",
      "city": "Portsmouth",
      "optedin": true
    },
    ...
]

2 1つだけ構造が全く違うパターン（上記のファイルに、下記ファイルを混ぜて同期）

[
  {
    "id": 0,
    "name": "Neida Shelly",
  },
  {
    "address": "53 Lake Drive",
    "city": "Newcastle",
    "optedin": true
  },
  {
    "id": 2,
    "name": "Jamie Fennell",
    "work": {
      "main": "会社員",
      "sub": "ギャンブラー"
    },
    "email": "[email protected]",
    "dob": "1942",
    "address": "74 Main Street",
    "city": "Christchurch",
    "optedin": true
  }
]

3 構造は同じだけど、1つだけ階層が深いパターン（1のファイル群に、下記ファイルを混ぜて同期）

[
  {
    "id": 0,
    "name": "Benito Webster",
    "work": "CoreMax",
    "email": "[email protected]",
    "dob": "1954",
    "address": "95 Brook Street",
    "city": "Atlanta",
    "optedin": true
  },
  {
    "id": 1,
    "name": "Travis Miers",
    "work": {
      "main": "会社員",
      "sub": "ギャンブラー"
    },
    "email": "[email protected]",
    "dob": "1960",
    "address": "94 Tavistock Place",
    "city": "Stanford",
    "optedin": false
  }
]